package com.santiao.youxiang.database.hleper

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
//import com.appyou.xiangai.APP
//import com.appyou.xiangai.bean.lzy.HistoryBean
import com.santiao.youxiang.bean.PostBean
import com.santiao.youxiang.bean.lzy.HistoryBean
import com.santiao.youxiang.ui.fragment.HomeViewModel
import kotlin.apply
import kotlin.text.trimIndent
import kotlin.toString

// 表名
const val TABLE_POST = "post"

// 列名
const val COLUMN_POST_ID = "pid"
const val COLUMN_POST_ADDRESS = "pAddress"
const val COLUMN_POST_CODE = "pCode"
const val COLUMN_POST_CURRENT = "isCurrent"

class PostDatabaseHelper(context: Context,  var homeViewModel: HomeViewModel) : SQLiteOpenHelper(context, "post_management.db", null, 1,) {
//    private val HviewModel: HomeViewModel by viewModels()

    override fun onCreate(db: SQLiteDatabase) {
        // 创建 Post 表
        val createPostTable = """
            CREATE TABLE $TABLE_POST (
                $COLUMN_POST_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_POST_ADDRESS TEXT NOT NULL,
                $COLUMN_POST_CODE TEXT NOT NULL,
                $COLUMN_POST_CURRENT INTEGER NOT NULL
            )
        """.trimIndent()
        db.execSQL(createPostTable)

        val createHistoryTable = """
            CREATE TABLE history (
                hid INTEGER PRIMARY KEY AUTOINCREMENT,
                keywords TEXT NOT NULL,
                createTime INTEGER NOT NULL
            )
        """.trimIndent()
        db.execSQL(createHistoryTable)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // 升级时删除表并重新创建
        db.execSQL("DROP TABLE IF EXISTS $TABLE_POST")
        onCreate(db)
    }

    // 插入 Post 数据
    fun insertPost(post: PostBean): Long {
        val db = this.writableDatabase
        val contentValues = ContentValues().apply {
            put(COLUMN_POST_ADDRESS, post.pAddress)  // 插入邮箱地址
            put(COLUMN_POST_CODE, post.pCode)  // 插入授权码
            put(COLUMN_POST_CURRENT, if (post.isCurrent) 1 else 0)  // 插入是否为默认邮箱
        }
        val result= db.insert(TABLE_POST, null, contentValues)
        homeViewModel.postList.value=getAllPosts()
        return result
    }

    // 获取所有 Post 数据
    fun getAllPosts(): MutableList<PostBean> {
        val db = this.readableDatabase
        val cursor: Cursor = db.query(
            TABLE_POST,
            null,  // 获取所有列
            null,  // 不设置条件
            null,
            null,
            null,
            "$COLUMN_POST_ID ASC" // 按照 pid 升序排序
        )

        val posts = mutableListOf<PostBean>()
        if (cursor.moveToFirst()) {
            do {
                val pid = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_POST_ID))  // 获取 pid
                val pAddress = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_POST_ADDRESS))  // 获取邮箱地址
                val pCode = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_POST_CODE))  // 获取授权码
                val isCurrent = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_POST_CURRENT)) == 1  // 获取是否为默认邮箱
                posts.add(PostBean(pid, pAddress, pCode, isCurrent))  // 添加到列表
            } while (cursor.moveToNext())
        }
        cursor.close()
        return posts  // 返回所有邮箱数据
    }

    // 获取当前默认的邮箱（isCurrent = 1）
    fun getCurrentPost(): PostBean? {
        val db = this.readableDatabase
        val cursor: Cursor = db.query(
            TABLE_POST,
            null,
            "$COLUMN_POST_CURRENT = ?",  // 查询条件：是否为默认邮箱
            arrayOf("1"),  // 只获取当前默认邮箱
            null,
            null,
            null
        )

        return if (cursor.moveToFirst()) {
            val pid = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_POST_ID))  // 获取 pid
            val pAddress = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_POST_ADDRESS))  // 获取邮箱地址
            val pCode = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_POST_CODE))  // 获取授权码
            val isCurrent = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_POST_CURRENT)) == 1  // 获取是否为默认邮箱
            PostBean(pid, pAddress, pCode, isCurrent)  // 返回当前默认邮箱
        } else {
            null  // 没有找到默认邮箱
        }
    }

    // 更新 Post 数据
    fun updatePost(post: PostBean): Int {
        val db = this.writableDatabase
        val contentValues = ContentValues().apply {
            put(COLUMN_POST_ADDRESS, post.pAddress)  // 更新邮箱地址
            put(COLUMN_POST_CODE, post.pCode)  // 更新授权码
            put(COLUMN_POST_CURRENT, if (post.isCurrent) 1 else 0)  // 更新是否为默认邮箱
        }
        val result= db.update(
            TABLE_POST,  // 表名
            contentValues,  // 更新的数据
            "$COLUMN_POST_ID = ?",  // 更新条件：根据 pid
            arrayOf(post.pid.toString())  // 传入的 pid
        )
        homeViewModel.postList.value=getAllPosts()
        return result
    }

    fun setFirstPostAsCurrent(): Long? {
        val db = this.writableDatabase
        var selectedPid: Long? = null

        db.beginTransaction()
        try {
            // 1. 先将所有 isCurrent 设置为 false
            db.execSQL("UPDATE $TABLE_POST SET $COLUMN_POST_CURRENT = 0")

            // 2. 查询 pid 最小的记录
            val cursor = db.rawQuery("SELECT $COLUMN_POST_ID FROM $TABLE_POST ORDER BY $COLUMN_POST_ID ASC LIMIT 1", null)
            if (cursor.moveToFirst()) {
                selectedPid = cursor.getLong(0) // 获取最小 pid
                // 3. 更新 isCurrent 为 true
                db.execSQL("UPDATE $TABLE_POST SET $COLUMN_POST_CURRENT = 1 WHERE $COLUMN_POST_ID = ?",
                    arrayOf(selectedPid)
                )
            }
            cursor.close()

            // 提交事务
            db.setTransactionSuccessful()
        } finally {
            db.endTransaction()
        }

        // 更新 ViewModel 数据
        homeViewModel.postList.value = getAllPosts()

        return selectedPid
    }

    // 删除 Post 数据
    fun deletePost(postId: Long): Int {
        val db = this.writableDatabase
        val result= db.delete(TABLE_POST, "$COLUMN_POST_ID = ?", arrayOf(postId.toString()))  // 根据 pid 删除数据
        return result
    }

    // 判断指定邮箱地址是否已存在
    fun isPostAddressExists(emailAddress: String): Boolean {
        val db = this.readableDatabase
        val cursor: Cursor = db.query(
            TABLE_POST,
            null,  // 获取所有列
            "$COLUMN_POST_ADDRESS = ?",  // 查询条件：邮箱地址是否匹配
            arrayOf(emailAddress),  // 邮箱地址作为参数
            null,
            null,
            null
        )

        val exists = cursor.count > 0  // 如果查询结果的行数大于0，表示邮箱地址已存在
        cursor.close()  // 关闭游标
        return exists  // 返回结果
    }

    // ① 根据 createTime 由近到远排序返回 HistoryBean 可变列表
    fun getAllHistoryDesc(): MutableList<HistoryBean> {
        val db = this.readableDatabase
        val cursor = db.query(
            "history",
            null,
            null,
            null,
            null,
            null,
            "createTime DESC"
        )

        val historyList = mutableListOf<HistoryBean>()
        if (cursor.moveToFirst()) {
            do {
                val hid = cursor.getLong(cursor.getColumnIndexOrThrow("hid"))
                val keywords = cursor.getString(cursor.getColumnIndexOrThrow("keywords"))
                val createTime = cursor.getLong(cursor.getColumnIndexOrThrow("createTime"))
                historyList.add(HistoryBean(hid, keywords, createTime))
            } while (cursor.moveToNext())
        }
        cursor.close()
        return historyList
    }

    // ② 判断表内是否存在 keywords，如果存在则更新 createTime，不存在则插入一条记录
    fun insertOrUpdateHistory(history: HistoryBean) {
        val db = this.writableDatabase
        val cursor = db.query(
            "history",
            arrayOf("hid"),
            "keywords = ?",
            arrayOf(history.keywords),
            null,
            null,
            null
        )

        val contentValues = ContentValues().apply {
            put("keywords", history.keywords)
            put("createTime", history.createTime)
        }

        if (cursor.moveToFirst()) {
            val hid = cursor.getLong(cursor.getColumnIndexOrThrow("hid"))
            db.update("history", contentValues, "hid = ?", arrayOf(hid.toString()))
        } else {
            db.insert("history", null, contentValues)
        }

        cursor.close()
    }

    // ③ 删除所有记录
    fun deleteAllHistory() {
        val db = this.writableDatabase
        db.execSQL("DELETE FROM history")
    }

}
